package com.itheima.dao;

import com.itheima.pojo.Member;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface MemberDao {
    @Insert("insert into t_member (fileNumber,name,sex,idCard,phoneNumber, regTime,password,email,birthday,remark) values (#{fileNumber},#{name},#{sex},#{idCard},#{phoneNumber}, #{regTime},#{password},#{email},#{birthday},#{remark})")
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    public int saveVip(Member member);
    //根据手机号查询用户
    @Select("select * from t_member where phoneNumber=#{telephone}")
    Member findByPhone(String telephone);

    //查询时间
    @Select("SELECT DISTINCT(DATE_FORMAT(regTime,'%Y-%m')) AS '日期' FROM t_member WHERE regTime < #{date} OR  DATE_FORMAT(regTime,'%Y-%m') =#{date}  ORDER BY regTime DESC LIMIT 0,5")
    List<String> findOneYealTime(String date);
    @Select("SELECT COUNT(id) FROM t_member WHERE regTime LIKE #{date}")
    int findCountBYTime(String date);
    @Select("select name from t_setmeal")
    List<String> getSetmealReport();
    //@Select("SELECT COUNT(t1.id) FROM t_order t1,t_setmeal t2 WHERE t1.setmeal_id=t2.id AND t2.id=(select id from t_setmeal where name=#{setmealName})")
    @Select("SELECT t2.name,COUNT(t1.id) AS value FROM t_order t1,t_setmeal t2 WHERE t1.setmeal_id=t2.id GROUP BY t2.name")
    List<Map<String,Object>> findCountByName();
    //获取今天新注册的会员
    @Select("SELECT count(id) FROM t_member WHERE regTime=#{today}")
    int findTodayNewVip(String today);
    //查询本周注册的会员数
    @Select("SELECT count(id) FROM t_member WHERE regTime>#{mondayWeek} OR regTime =#{mondayWeek}")
    int findWeekVipFindByTime(String mondayWeek);
    //查询本月注册的会员数
    @Select("SELECT count(id) FROM t_member WHERE regTime>#{firdayMonth} OR regTime=#{firdayMonth}")
    int findMonthVipFindByTime(String firdayMonth);
    //查询总注册会员数
    @Select("SELECT count(id) FROM t_member")
    int findAllVip();
    //查看今日到诊数量
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate = #{today} AND orderStatus='已到诊'")
    int findTodayVisitsNum(String today);
    //查看本周一到当前的到诊数量 已到诊
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{mondayWeek} AND orderStatus='已到诊'")
    int findThisWeekVisitsNum(String mondayWeek);
    //查看本月第一天到当前的诊数量
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{firdayMonth} AND orderStatus='已到诊'")
    int findThisMonthVisitsNum(String firdayMonth);
    //查询今天预约的人数
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate = #{today} ")
    int findOrderByThisToday(String today);
    //查询本周预约的人数
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{mondayWeek} ")
    int findOrderByThisWeek(String mondayWeek);
    //查询本月预约的人数
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{firdayMonth} ")
    int findOrderByThisMonth(String firdayMonth);
   @Select("SELECT t1.name,COUNT(t1.NAME) setmeal_count,COUNT(t1.NAME)/(SELECT COUNT(id) FROM t_order) proportion,t1.remark  FROM t_setmeal t1,t_order t2 WHERE t1.`id`=t2.`setmeal_id` GROUP BY NAME ORDER BY setmeal_count DESC LIMIT 0,4")
    List<Map> findHotSetmeal();
}
